use master
go
create database Student
on
(
	name='Student',
	filename='D:\SQL\Student.mdf',
	size=5MB,
	maxsize=50MB,
	filegrowth=10%
)
log on
(
	name='Student_log',
	filename='D:\SQL\Student_log.ldf',
	size=5MB,
	maxsize=50MB,
	filegrowth=10%
)
go

use Student
go
create table StuInfo
(
	StuNO char(10) primary key ,
	StuName nvarchar(20) not null,
	StuAge int not null ,
	StuAddress nvarchar(100),
	StuSeat int not null,
	StuSex char(1) check(StuSex in('1','0')) not null ,
)

create table examInfo
(
	examNO int primary key identity(1,1),
	StuNO char(10) references StuInfo(StuNO),
	writtenExam int check(writtenExam>=0 and writtenExam<=100) not null,
	labExam int check(labExam>=0 and labExam<=100) not null
)
go

insert into StuInfo values
('s2501','张秋利',20,'美国硅谷',1,'1'),
('s2502','李斯文',18,'湖北武汉',2,'0'),
('s2503','马文才',22,'湖北长沙',3,'1'),
('s2504','欧阳俊熊',21,'湖北武汉',4,'0'),
('s2505','梅超风',20,'湖北武汉',5,'1'),
('s2506','陈旋风',19,'美国硅谷',6,'1'),
('s2507','陈风',20,'美国硅谷',7,'0')

insert into examInfo (writtenExam,labExam) values
(50,70),
(60,65),
(86,85),
(40,80),
(70,90),
(85,90)

go

--1
select 学号=StuNO ,姓名=StuName ,年龄=StuAge,地址=StuAddress,座位号=StuSeat,性别=StuSex from StuInfo

--2
select StuName,StuAge,StuAddress from StuInfo

--3
select 学号=StuNO,笔试=writtenExam,机试=labExam from examInfo
select StuNO as 学号,writtenExam as 笔试,labExam as 机试 from examInfo
select StuNO 学号,writtenExam 笔试,labExam 机试 from examInfo

--4
select 邮箱=StuNO+StuName+'@'+StuAddress from StuInfo

--5
select StuNO,writtenExam,labExam,总分=writtenExam+labExam from ExamInfo

--6
select distinct StuAddress from StuInfo

--7
select distinct 所有年龄=StuAge from StuInfo

--8
select top 3 * from StuInfo 

--9
select top 4 * from  StuInfo order by StuName , StuSeat

--10
select top 50 percent * from StuInfo

--11
select * from StuInfo where StuAddress='湖北武汉' and StuAge=20

--12
select * from examInfo where writtenExam>=60 and writtenExam<=80  order by writtenExam ASC

--13
select * from StuInfo where StuAddress='湖北武汉' or StuAddress='湖北长沙'
select * from StuInfo where StuAddress in('湖北武汉','湖北长沙')

--14
select * from examInfo where writtenExam<70 or writtenExam>90 order by writtenExam DESC

--15 
select * from StuInfo where StuAge is null or StuAge=''

--16
select * from StuInfo where StuAge like '%'

--17
select * from StuInfo where StuName like '张%'

--18
select * from StuInfo where StuAddress like '%湖%'

--19
select * from StuInfo where StuName like '张_'

--20
select * from StuInfo where StuName like '__俊%'

--21
select * from StuInfo order by StuAge ASC

--22
select * from StuInfo order by StuAge DESC , StuSeat ASC

--23
select top 1 * from examInfo order by writtenExam DESC

--24
select top 1 * from examInfo order by labExam ASC